package com.floretexaminaction.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@Service
@Transactional
@Slf4j
public class UserService {

    Log log = LogFactory.getLog("三岁小仙仙");

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 批量插入
     * 速度有点慢
     * @param list 实体类集合
     * @param <T>  表对应的实体类
     */
    public <T> void batchInsert(List<T> list) {
        if (!ObjectUtils.isEmpty(list)) {
            for (int i = 0; i < list.size(); i++) {
                entityManager.persist(list.get(i));
                if (i % 50 == 0) {
                    entityManager.flush();
                    entityManager.clear();
                }
            }
            entityManager.flush();
            entityManager.clear();
        }
    }

    /**
     * 批量更新
     *
     * @param list 实体类集合
     * @param <T>  表对应的实体类
     */
    public <T> void batchUpdate(List<T> list) {
        if (!ObjectUtils.isEmpty(list)) {
            for (int i = 0; i < list.size(); i++) {
                entityManager.merge(list.get(i));
                if (i % 50 == 0) {
                    entityManager.flush();
                    entityManager.clear();
                }
            }
            entityManager.flush();
            entityManager.clear();
        }
    }

    // ***********************************************************************************************************
    /**
     * 字符串第一个字符大写
     * @param name
     * @return
     */
    public static String captureName(String name) {
        char[] cs=name.toCharArray();
        //字符a对应数字97
        cs[0]-=32;
        return String.valueOf(cs);
    }

    /**
     * 首字母小写
     * @param str
     * @return
     */
    public static String getFirstA(String str){
        String name = str.toLowerCase();
        char[] cs=name.substring(0, 1).toCharArray();
        return String.valueOf(cs[0])+"_";
    }

    /**
     * 解析属性为对象的本地类，p_name
     * @param str
     */
    public static void analysisObject(String str,StringBuffer sb,Field[] fs,int k,Object o){
        try {
            String content = getFirstA(fs[k].getName());
            Class c1 = Class.forName(str);

            Field[] f1 = c1.getDeclaredFields();
            for (int l = 0; l < f1.length; l++) {
                StringBuffer suf = new StringBuffer("get");
                suf.append(captureName(f1[l].getName()));
                Method me = c1.getDeclaredMethod(suf.toString());
                Object ob = me.invoke(o);
                if(ob!=null){
                    String str1 = f1[l].getType().getName();
                    Entity entity = f1[l].getAnnotation(Entity.class);
                    if(entity!=null){
                        //判断是否是对象
                        //if(isMyObject(str)){
                        analysisObject(str1,sb,f1,l,ob);
                    }else{
                        sb.append(",");
                        sb.append("\""+content+f1[l].getName()+"\":\""+ob+"\"");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量插入数据
     * @param list 泛型List
     * @param <T> 实体类
     * Class<?> aClass = Class.forName(list.get(0).getClass().getName());
     * String tableName = aClass.getAnnotation(Entity.class).name();
     * 注意：如果使用以上两个方法获取表名，则必需在对应实体类中，添加注释@Entity(name="[表名]")
     */
    public <T> String saveBatch(List<T> list, String tableName) {
        StringBuilder insert = new StringBuilder("insert into `");
        //json格式  [{"":"","":""},{}]
        StringBuffer sb = new StringBuffer("[");
        Class c = null;
        Field[] fieldNames = null;
        try {
//            Class<?> aClass = Class.forName(list.get(0).getClass().getName());
//            String tableName = aClass.getAnnotation(Entity.class).name();
            insert.append(tableName).append("` (");
            for(int i = 0 ; i < list.size() ; i++){
                c = Class.forName(list.get(i).getClass().getName());

                Field[] fs = c.getDeclaredFields();
                fieldNames = fs;
                sb.append("{");
                for (int j = 0 ; j < fs.length ; j++){
                    Object objs = list.get(i);
                    StringBuffer suf = new StringBuffer("get");
                    suf.append(captureName(fs[j].getName()));
                    Method me = c.getDeclaredMethod(suf.toString());
                    Object o = me.invoke(objs);
                    if(o!=null){
                        String str = fs[j].getType().getName();
                        //注解判断属性是否是本地类
                        Entity entity = fs[j].getAnnotation(Entity.class);
                        if(entity!=null){
                            //判断是否是对象
                            //if(isMyObject(str)){
                            analysisObject(str,sb,fs,j,o);
                            log.info(entity.name());
                        }else{
                            if(j!=0){
                                sb.append(",");
                            }
                            sb.append("\""+fs[j].getName()+"\":\""+o+"\"");
                        }
                    }
                }
                sb.append("}");
                if(i!=list.size()-1){
                    sb.append(",");
                }
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        sb.append("]");

        // 拼接字段名 到 sql语句中
        for(int i = 0 ; i < fieldNames.length ; i++){
            insert.append(fieldNames[i].getName())
                    .append(",");
            if (i == (fieldNames.length-1)){
                insert.deleteCharAt(insert.length()-1);
                insert.append(") values ");
            }
        }

        // 拼接插入的values数据 到 sql语句中
        JSONArray objects = JSONArray.parseArray(sb.toString());
        for (int p = 0 ; p < objects.size() ; p++){
            JSONObject jsonObject = JSONObject.parseObject(objects.get(p).toString());
            insert.append("(");
            for (int i = 0 ; i < fieldNames.length ; i++){
                insert.append("'")
                        .append(jsonObject.get(fieldNames[i].getName()))
                        .append("'");
                if (i != (fieldNames.length-1)){
                    insert.append(",");
                }
            }
            insert.append(")");
            if (p != (objects.size()-1)){
                insert.append(",");
            }
        }
        try {
            // 执行拼接后的sql语句
            jdbcTemplate.execute(insert.toString());
            return "true";
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            return e.getMessage().substring(e.getMessage().indexOf(":")+1);
        }
    }

    /**
     * 创建班级
     * @param tableNumber 班级号
     * @param tableName 班级名称
     * @return 返回true/error
     */
    public String createClass(String tableNumber, String tableName){
        String table = "create table `"+tableNumber+"` (\n" +
                "\tstudentName varchar(15) not null,\n" +
                "\tstudentNumber varchar(15) not null,\n" +
                "\tstudentPass varchar(100) not null,\n" +
                "\tstudentEmail varchar(30) not null,\n" +
                "\tprimary key(studentNumber)\n" +
                ")comment=\""+tableName+"\";";

        try {
            // 执行拼接后的sql语句
            jdbcTemplate.execute(table);
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            return e.getMessage().substring(e.getMessage().indexOf(":")+1);
        }
        return "true";
    }

    /**
     * 删除班级
     * ---可公用---
     * 根据表名，删除表
     * @param classNumber 班级号
     */
    public String deleteClass(String classNumber){
        String sql = "drop table `"+classNumber+"`;";
        try {
            // 执行拼接后的sql语句
            jdbcTemplate.execute(sql);
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            return e.getMessage().substring(e.getMessage().indexOf(":")+1);
        }
        return "true";
    }

    /**
     * 返回一个JSON类型的数组对象[{"":""},{"":""}]
     * @param list 需要转换的类型为 List<Map<String, Object>>
     * @return 数组对象
     */
    private List swapList(List<Map<String, Object>> list){
        List retList = new ArrayList();
        for (int i = 0 ; i < list.size() ; i++){
            String str = list.get(i).toString().replace("=", ":'");
            str = str.replace(",","',");
            str = str.replace("}","'}");
            retList.add(JSON.toJSON(str));
        }
        return retList;
    }

    /**
     * 根据班级号 查询班级所有学生信息
     * ---可公用---
     * 根据表名，查询表中所有信息
     * @param classNumber 班级号
     * @return tableAll
     */
    public String queryClassStuFindNumber(String classNumber){
        String sql = "select * from `"+classNumber+"`";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return swapList(list).toString();
    }

    /**
     * 根据班级号 和 学号 查询学生信息
     * @param className 班级号
     * @param studentNumber 学号
     * @return 数组对象
     */
    public String queryClassStudent(String className, String studentNumber){
        String sql = "select * from `"+className+"` where studentNumber = "+studentNumber+"";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return swapList(list).toString();
    }

    /**
     * 根据班级号 和 学号删除学生信息
     * @param classNumber 班级号
     * @param studentNumber 学号
     */
    public void byClassNumberOrStudentNumberDelete(String classNumber, String studentNumber){
        String sql = "delete from `"+classNumber+"` where studentNumber = "+studentNumber+" ";
        jdbcTemplate.execute(sql);
        try {
            // 执行拼接后的sql语句
            jdbcTemplate.execute(sql);
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
        }
    }

    /**
     * 教师添加考试课程
     * @param selectClass 选择课程所属班级
     * @param coursesName 课程名称
     * @param option 操作类型 true为添加课程、false为删除课程
     * @return true或错误信息
     */
    public String teacherAddCourses(String selectClass, String coursesName, boolean option){
        String addCourses = "alter table `"+selectClass+"` add column "+coursesName+" float(3,1) comment '"+coursesName+"'";
        String delCourses = "alter table `"+selectClass+"` drop column "+coursesName;
        try {
            // 执行拼接后的sql语句
            if(option){
                jdbcTemplate.execute(addCourses);
            }else{
                jdbcTemplate.execute(delCourses);
            }
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            String error = e.getMessage().substring(e.getMessage().indexOf(":")+1);
            return error;
        }
        return "true";
    }

    /**
     * 根据不同教师工号查询教师创建的考试
     * @param teacherNumber 教师工号
     * @return 数组对象
     */
    public String findTeacherCreateExamination(String teacherNumber){
        String sql = "select e.*, c.className, t.teacherName from " +
                "(examinationRecord e inner join classManage c on e.classNumber = c.classNumber) " +
                "inner join teacher t on t.teacherNumber = e.teacherNumber  " +
                "where e.teacherNumber = '"+teacherNumber+"'";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return swapList(list).toString();
    }

    /**
     * 删除考试记录表中的数据
     * @param teacherNumber 教师工号
     * @param classNumber 班级号
     * @param coursesName 课程名
     * @return 删除数量
     */
    public int deleteExamination(String teacherNumber, String classNumber, String coursesName){
        String sql  = "delete from examinationRecord where teacherNumber = "+teacherNumber+" and classNumber = "+classNumber+" and coursesName = '"+coursesName+"'";
        int update = jdbcTemplate.update(sql);
        return update;
    }

    /**
     * 统计班级人数
     * @param classNumber 班级号
     * @return int数值
     */
    public int countClassPeopleNumebr(String classNumber){
        String sql = "select count(*) from `"+classNumber+"`";
        int count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

    /**
     * 编辑考试记录信息
     * @param teacherNumber 教师工号
     * @param classNumber 班级号
     * @param coursesName 课程名
     * @param startTime 更新的开始的时间
     * @param endTime 更新的结束时间
     * @return 更新数
     */
    public int editExamination(String teacherNumber, String classNumber, String coursesName, String startTime, String endTime){
        String sql = "update examinationRecord set startTime = '"+startTime+"', endTime = '"+endTime+"' where teacherNumber = "+teacherNumber+" and classNumber = "+classNumber+" and coursesName = '"+coursesName+"'";
        int update = jdbcTemplate.update(sql);
        return update;
    }

    /**
     * 创建试题库
     * @param questionBankName 试题库名
     * @return true或失败消息
     */
    public String createQuestionBank(String questionBankName){
        String table = "CREATE TABLE `"+questionBankName+"` (\n" +
                "  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',\n" +
                "  `questionName` varchar(200) COLLATE utf8mb4_general_ci NOT NULL COMMENT '试题题目',\n" +
                "  `type` varchar(10) COLLATE utf8mb4_general_ci NOT NULL COMMENT '题型',\n" +
                "  `score` int(11) NOT NULL COMMENT '分值',\n" +
                "  `optionList` varchar(300) COLLATE utf8mb4_general_ci NOT NULL COMMENT '选项列表',\n" +
                "  `rightOption` varchar(300) COLLATE utf8mb4_general_ci NOT NULL COMMENT '正确选项',\n" +
                "  PRIMARY KEY (`ID`)\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";
        try {
            // 执行拼接后的sql语句
            jdbcTemplate.execute(table);
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            return e.getMessage().substring(e.getMessage().indexOf(":")+1);
        }
        return "true";
    }

    /**
     * 创建学生试题作答库--保存学生作答信息
     * @param answerBank 【班级号+试题库号】= 作答库号
     * @return true或失败消息
     */
    public String createStudentAnswerBank(String answerBank){
        String sql = "CREATE TABLE `"+answerBank+"` (\n" +
                "  `studentNumber` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,\n" +
                "  `rightOption` varchar(300) COLLATE utf8mb4_general_ci DEFAULT NULL,\n" +
                "  PRIMARY KEY (`studentNumber`)\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";
        try {
            // 执行拼接后的sql语句
            jdbcTemplate.execute(sql);
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            return e.getMessage().substring(e.getMessage().indexOf(":")+1);
        }
        return "true";
    }

    /**
     * 保存学生作答信息
     * @param tableName 表名
     * @param data 字符数组对象
     * @return true或失败消息
     */
    public String saveStudentAnswerBank(String tableName, String data){
        StringBuffer sql = new StringBuffer("insert into `"+tableName+"` values ");
        JSONArray jsonArray = JSONArray.parseArray(data);
        for (int i = 0 ; i < jsonArray.size() ; i++){
            JSONObject jsonObject = JSONObject.parseObject(jsonArray.get(i).toString());
            if(i!=0 && i!=jsonArray.size()){sql.append(",");};
            sql.append("('").append(jsonObject.get("studentNumber")).append("',");
            sql.append("'").append(jsonObject.get("rightOption")).append("')");
        }
        try {
            // 执行拼接后的sql语句
            jdbcTemplate.execute(sql.toString());
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            return e.getMessage().substring(e.getMessage().indexOf(":")+1);
        }
        return "true";
    }

    /**
     * 保存学生考试科目的成绩
     * @param classNumber 班级号
     * @param coursesName 课程名（科目）
     * @param studentResult 学生学号-成绩
     * @return 更新数或失败消息
     */
    public String saveStudentExaminationResult(String classNumber, String coursesName ,String studentResult){
        JSONArray jsonArray = JSONArray.parseArray(studentResult);
        try {
            for (int i = 0 ; i < jsonArray.size() ; i++){
                JSONArray jsonArray1 = JSONArray.parseArray(jsonArray.get(i).toString());
                String sql = "update `"+classNumber+"` set "+coursesName+"="+jsonArray1.get(1)+" where studentNumber="+jsonArray1.get(0);
                // 执行拼接后的sql语句
                jdbcTemplate.update(sql);
            }
            return "true";
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            return e.getMessage().substring(e.getMessage().indexOf(":")+1);
        }
    }


    /**
     * 删除试题库所有数据
     * ---可公用---
     * 根据表名，删除表中所有数据
     * @param questionBankName 试题库号
     */
    public String deleteQuestionBank(String questionBankName){
        String sql = "delete from `"+questionBankName+"`";
        try {
            // 执行拼接后的sql语句
            jdbcTemplate.execute(sql);
        } catch (Exception e) {
            log.error("sql解析错误" + e.getMessage());
            return e.getMessage().substring(e.getMessage().indexOf(":")+1);
        }
        return "true";
    }

    /**
     * 查询班级某一科目的所有学生成绩
     * @param classNumber 班级号
     * @param coursesName 课程名（科目）
     * @return jsonArray或错误消息
     */
    public String autoGetClassStudentCoursesResult(String classNumber, String coursesName){
        String sql = "select studentNumber, studentName, "+coursesName+" from `"+classNumber+"`";
        try {
           // 执行拼接后的sql语句
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            return JSON.toJSONString(swapList(maps));
        } catch (Exception e) {
           log.error("sql解析错误" + e.getMessage());
           return e.getMessage().substring(e.getMessage().indexOf(":")+1);
       }
    }

}
